Configuring Inbound EDI Transactions

Configuring Inbound EDI Transactions Standards Formatting structures Communication methods Available options in DEACOM Configuration Process Using FTPS vs SFTP setup Adding an EDI Import Configuring the EDI mapping EDI Import examples EDI import example (850 Import) Bill-to PO Number Order Description Ship-To Customer ID Part Number ID Quantity Ordered EDI import example using the "New Order Same-As Current Order" option EDI import example that uses the "Delete Line Condition" option Creating customers during EDI Imports Creating a Ship-To customer using Bill-To customer default values EDI processing associated with customer creation Using EDI to apply payments Update EDI sales/purchase orders after shipment and invoicing Automatically mark purchase orders with non-stocked items as received and invoiced Automatically ship sales orders during EDI Imports Automatically receive purchase orders during EDI Imports Adjust inventory via EDI Imports Ship As Reserved a Sales Order via EDI Import Reserve Inventory to a Sales Order via EDI Import Invoice a shipped sales order via EDI Import Ship and invoice a sales order in the same EDI profile/map Apply NET1 credit card payments to sales orders via EDI Allowing duplicate Bill-to or Ship-to PO numbers on EDI Imports Setting duplicate Bill-to PO numbers Setting duplicate Ship-to PO numbers Creating inter-company transfers via EDI imports Setting Tax Regions on sales orders of new Bill-To/ Ship-To Companies during EDI Import Tax Region Parsing Rule Example Setting General Ledger Overrides for new Bill-To/Ship-To Companies during EDI Import Running EDI Imports Handling EDI Import Errors Notifying Users of EDI Failures via System Triggers FAQ & Diagnostic Tips Does DEACOM partially process EDI files? How does the system report EDI failures if a Bill-to or Ship-to lookup/creation fails? Does DEACOM require the use of a VAN to facilitate EDI transactions with our trading partners? Does DEACOM facilitate AS2 transactions? Can I look-up a customer part cross reference when I only have the part number? How do I use today's date to populate a field? Does DEACOM maintain remit to information that can be used in EDI Transactions? How does EDI handle sales order promotions? How can I import and EDI file containing multiple order types? Can I indicate taxes owed for order lines when running 810 imports? Do import maps need to have unique names?

EDI stands for “Electronic Data Interchange”. The EDI process involves using computers to complete transactions electronically between two organizations, called “Trading Partners”. There are three major processes involved in the exchange of EDI data:

• Mapping – Involves transforming an EDI document into another format. Mapping is essential for proper system integration.
• Translation – The process of accepting inbound EDI data or preparing an outbound file for transmission.
• Communications – The transmission of the EDI transaction. This can be done indirectly through a VAN (“Value Added Network”) or directly via an FTP or FTPS connection.

Deacom provides a highly flexible and configurable EDI solution allowing companies to maximize the value of implementing EDI transactions. Any outbound transaction can be configured within DEACOM. A list of DEACOM-supported inbound and traditional outbound EDI transactions are shown below:

Transaction	Common ID	Inbound/Outbound
Invoice	810/880	Outbound
Purchase Order	850/875	Inbound
Purchase Order Acknowledgement	855	Outbound
Advanced Shipment Notice	856	Outbound
Functional Acknowledgement	997	Outbound

An example of an EDI import would be an inbound Purchase Order, referred to as an 850. The data file from the trading partner would include the desired purchase quantity and the part number as well as additional information. This information would be mapped to the appropriate fields and tables in DEACOM and imported into the system, resulting in the generation of a sales order to fulfill the trading partners order amount. This best practice will be review the configuration and steps to set up inbound EDI transactions. When importing 850 SO EDIs, the import map will now execute product ID lookups in one of two ways:

• UPC Table (p4_codenum). The UPC lookup stores the UPC # in the "UPC Number" field on the SO line.
• Cust Xref (cu_codenum). The cu_prid lookup stores the Cust Xref # in the "Cust. Part Number" field on the SO line.

Additional information regarding EDI Standards and formatting structures of EDI is available via EDI Imports.

Standards

In the United States, organizations traditionally follow the ASI standards (ANSI ASC X12_) for EDI document formats. Outside the United States, the international EDI standard EDIFACT. DEACOM provides a configurable setup to allow companies to tailor their EDI setup to support the two major EDI standards.

• Note: Although there are global standards, the EDI setup in DEACOM still needs to be configured for individual companies based on their business processing rules. Often times, trading partners or their VANs only partially conform with the standards, use absolute custom formats, or conform with the standards, but have special bits and pieces of information that must be stored and returned on an acknowledgement notice.

Formatting structures

EDI documents can be formatted in a variety of ways based on the industry, trading partner, and VAN. DEACOM supports a number of different formatting structures:

• Microsoft Excel Spreadsheet
• XML
• CSV
• Fixed Length
• Field Delimited (ASCII, comma, pipe, tab, space, other)

Communication methods

EDI transactions can be completed via a number of different communication protocols or methods. DEACOM supports the following protocols for communication via a VAN or directly to the trading partner:

• FTP (File Transfer Protocol) with an SSL certificate instead of through an SSH tunnel. FTPS is FTP over SSL and does NOT require a port number, whereas SFTP is FTP over SSH and does require a port number. Both are secure.

Available options in DEACOM

DEACOM offers the following EDI options and features.

• Import and creation of customers. DEACOM offers the ability to import bill-to and ship-to fields for the purpose of creating new customers to support the sales order EDI process. This feature is not meant to be used for the sole purpose of importing bill-to and ship-to master records into DEACOM.
• EDI import "New Order Same-As Current Order" option.
• Using EDI to apply payments.
• Update EDI sales orders after shipment and invoicing.
• Create Inter-Company Transfers via EDI Imports
• Create Purchase Orders or Purchase Requisitions via EDI Imports
• Automatically receive and invoice Purchase Orders with non-stock parts, including partial receipt of non-stocked items.

Configuration

The system configuration required for EDI depends on how companies will use the EDI features within DEACOM.

• If data will be exported from DEACOM, the tab will need to be configured.
• If data will be imported into DEACOM, the Source, Success, and Failure network paths/folders will need to be created to store the imported data files. These paths are then selected on EDI Import profiles.
• Note: While these folders can be named anything, it is a best practice to use the terms Source, Success, and Failure.
• Companies may also choose to create and maintain FTP servers for this purpose.
• In addition, any Customer defaults, Customer Part Cross References, and pricing should be configured prior to using EDI transactions so that the correct data is used for the order. Process information on these topics is available via Managing Customers, Understanding the DEACOM Pricing Hierarchy, and Configuring Sales Pricing Using Customer Part Cross References.

Notes:

• If setting up EDI for the first time, customers should perform the setup in a testing environment. The testing step consists of sending transactions between the trading partners’ testing environments. Once the communication is confirmed, the setup can be pushed to the production environment to be used live.
• DEACOM currently supports EDI Imports that provide access to sales order header, sales order line, sales assignments, purchase order header, purchase order line, and user-defined tables when creating new sales orders or purchase orders in the system. EDI Imports in DEACOM can be used to both create new sales or purchase orders and update existing sales or purchase orders provided they have not been shipped or received. When modifying existing orders, users will generally want to have a separate EDI Import profile that can be used to update existing orders. The system will use lookup conditions in the profile, i.e. order number, to locate the orders that need to be updated.

Process

Using FTPS vs SFTP setup

FTPS is FTP over SSL and does NOT require a port number, whereas SFTP is FTP over SSH and does require a port number. Both are secure.

Steps to update from sftp.spscommerce.net to ftps.spscommerce.com:

1. Firewalls must be configured to allow connection to: ftps.spscommerce.com (34.199.175.205, ports 21 and 10021).
2. The FTP Server in Import Maps and Triggers must be updated to replace sftp.spscommerce.net with ftps.spscommerce.com.
3. The rest of the FTP Server line must remain intact. For example, sftp.spscommerce.net/testout/850 would become ftps.spscommerce.com/testout/850.
4. SSH Protocol must be unchecked.
5. Accept Invalid Certificates For FTPS should be checked.
6. Use Binary FTP Mode should be unchecked.

Steps to update from sftp.spscommerce.net to sftp.spscommerce.com:

1. Current DEACOM version must support SFTP Port Numbers.
2. Firewalls must be configured to allow connection to: sftp.spscommerce.com (34.202.97.24, port 10022).
3. The FTP Server in Import Maps and Triggers must be updated to replace sftp.spscommerce.net with sftp.spscommerce.com.
4. The rest of the FTP Server line must remain intact. For example, sftp.spscommerce.net/testout/850 would become sftp.spscommerce.com/testout/850.
5. SFTP Port must be 10022.
6. SSH Protocol must be checked.
7. Accept Invalid Certificates For FTPS should be checked.
8. Use Binary FTP Mode should be unchecked.

Adding an EDI Import

1. Navigate to System > Maintenance > EDI Imports.
2. Click the "Add" button to display the EDI Import form. See the EDI Imports Encyclopedia page for details regarding the fields available to this form.
3. Fill in the appropriate information on this form. Required fields are marked in blue.
4. Next, click the "Add" button to display the Edit Parsing Rule form. See the Edit Parsing Rule help section for details regarding the fields on this form as well as the "Configuring the EDI mapping" section below.
5. The Edit Parsing Rule form defines the conditions or parsing rules for each line or import field on the EDI Import form. New parsing rules are added/modified using the “Add” or “Modify” buttons on the EDI Import form. DEACOM processes EDI Import data for sales orders or purchase orders in the following order, and must be taken into account when writing parsing rules: (This order mimics the steps required to manually place a Sales Order or Purchase Order)
   • Sales Order
   • Bill-to company
   • Bill-to header fields
   • Ship-to company
   • Ship-to header fields
   • All other order header fields
   • Header user fields
   • Order line part
   • Customer part
   • All other order line fields anything else (such as sales assignments)
   • Purchase Order
   • Vendor
   • Vendor header fields
   • All other order header fields
   • Header user fields
   • Order line part
   • Vendor part
   • All other order line fields
   • Anything else
6. Once all the necessary information has been entered on the Edit Parsing Rule form, click the "Save" and "Exit" buttons to return to the EDI Import form.
7. If additional parsing rules are needed, repeat steps 4-6.
8. Once all the information has been entered, click the "Save" and "Exit" buttons to complete the process.

Configuring the EDI mapping

As indicated previously, correctly mapping the incoming data to the corresponding fields in DEACOM is essential to ensure that EDI Imports work correctly. The configuration of an EDI transaction is based on the data format provided by a trading partner or VAN. Typically the trading partner or VAN will provide a sample file to work with during the initial configuration. The sample file may be accompanied by a guide indicating what type of information is stored in the data file. A simple line from an incoming data file is shown below. This file represents an 850 purchase order for EDI use.

• PO1*828*1439*FINI1*J99*5

At first glance, the user may not be able to quickly correlate the data in the file to the corresponding fields in DEACOM. In the example above, users would most likely recognize a product number or name, in this case “FINI1”, which represents a saleable finished good. As indicated above, users may use any reference material provided by the trading partner or VAN. In many cases, users will work their trading partner’s contact person to help in the mapping process.

Once users have determined the mapping between the incoming data and appropriate DEACOM fields, these mappings must be defined on the Edit Parsing Rule form as indicated in the screenshot below. In the example provided above, the correct mapping to be entered on the DEACOM Edit Parsing Rule is indicated in the table below.

Data File Field	Corresponding DEACOM Field	Type	Lookup Table	Lookup Expression	Field Expression	Notes
PO1	None	N/A	N/A	N/A	N/A	No parsing rule is needed for this data. The field is used to indicate the start of a new purchase order as indicated in the new order condition field on the "EDI Import" form.
828	to_biid (Bill-to Company ID)	Expression	N/A	N/A	828	Trading Partner provided the exact DEACOM Bill-to ID # in the data file.
1439	to_shid (Ship-to Company ID)	Expression	N/A	N/A	1439	Trading Partner provided the exact DEACOM Ship-to ID # in the data file.
FINI1	or_prid (Part Number ID)	Lookup	Item Master File (dmprod)	Pr_codenum = field4	Pr_id	The fourth field in the data file contains a value which is checked against the DEACOM item master table to verify the part is an active saleable part.
J99	to_billpo (Bill-to Company PO)	Expression	N/A	N/A	Field5	Indicates to the system that the information stored in the fifth field/column of the date file will be inserted into the to_billpo field on the DEACOM sales order.
5	or_quant (quantity ordered)	Expression	N/A	N/A	VAL(field6)	Indicates that the data stored in the sixth field/column of the data file will be used to populate the ordered quantity for this line on the sales order.

Note: In this example, an asterisk (*) is used as a field delimiter. This is indicated in the “Other Field Delimiter” field on the EDI Import form as seen on the screenshot above.

EDI Import examples

EDI import example (850 Import)

This example will cover an EDI that pulls an 850 PO from a network location and imports it as an Internet Sale into DEACOM.

Step 1. Define or identify the data to be imported. In this step we will use a text file representing an 850 PO. Below is how the sample file used in this example appears when viewed in Notepad:

Step 2. Setup the EDI Import Form. Navigate to System > Maintenance > EDI Imports > click "Add New" and fill in the EDI Import form as indicated below:

Field	Entry
Name	850 Import
Source Path	“Select an appropriate path”
Success Path	“Select an appropriate path”
Failure Path	“Select an appropriate path”
Order type	Internet Sale
Sample File	Leave blank for this example
Frequency (mins)	N/A for this example
FTP server	N/A for this example
FTP User Name	N/A for this example
FTP Password	N/A for this example
Field Delimiter	Other
Other Field Delim.	*
Line Delimiter	New line
Other Line Delim.	N/A for this example
New Order Cond.	field1 = “BEG”
New Order When	Order condition is true
New Line Condition	field1 = “PO1”
Skip Line Condition	N/A for this example
Template	Unchecked
Active	Checked
Accept invalid certificates for FTPS	Checked

Step 3. Setup the EDI Import Parsing Rules for each line of the import.

Now that the “header” portion of our EDI Import is complete we will add some fields to the import. Each EDI import needs only 4 pieces of data to create a sales order in DEACOM: Bill-to company PO number, Ship-to customer ID, Part number ID, and Quantity ordered. This example will include those 4 fields as well as the "Order description" field, which will be referenced as a condition when sending the invoice back to the trading partner.

Fill out the Edit Parsing Rule form for each of these fields below by first clicking on the "Add" button in the bottom left-hand corner of the EDI Import form and then saving once all information has been entered. This needs to be completed for each line on the EDI Import form.

Bill-to PO Number

Field	Entry
Field	Bill-to company PO
Conditions	field1 = "BEG"
Type	Expression
Lookup Table	N/A for this example
Lookup Expression	N/A for this example
Field Expression	field4

Order Description

Field	Entry
Field	Order Description
Conditions	field1 = "BEG"
Type	Expression
Lookup Table	N/A for this example
Lookup Expression	N/A for this example
Field Expression	”EDI Order”

Ship-To Customer ID

Field	Entry
Field	Ship-to company ID
Conditions	field1 = "N1" AND field2 = "ST"
Type	Lookup
Lookup Table	Ship to Companies
Lookup Expression	sh_custid = field5
Field Expression	sh_id

Note: Field 5 in the file is the Ship-to Alpha customer number.

Part Number ID

Field	Entry
Field	Part Number ID
Conditions	field1 = “PO1”
Type	Lookup
Lookup Table	UPC Numbers
Lookup Expression	P4_codenum = field8
Field Expression	P4_prid

Note: Field 8 in the file is the UPC number.

Field	Entry
Field	Part Number ID
Conditions	field1 = “PO1”
Type	Lookup
Lookup Table	Item Master File
Lookup Expression	pr_codenum = field8
Field Expression	pr_id

Note: Field 8 in this case would be the item master number.

Quantity Ordered

Field	Entry
Field	Quantity ordered
Conditions	field1 = “PO1”
Type	Expression
Lookup Table	N/A for this example
Lookup Expression	N/A for this example
Field Expression	VAL(field3)

Note: The VAL() function is needed to translate text to a number. Also, DEACOM is configured by default to translate zero quantities to 1 for EDI imports. Additional logic and skip line conditions must be written to ignore zero quantity lines.

Step 4. Run the EDI Import.

1. Ensure that the file is in the source folder as referenced on the EDI import. Also ensure that there is an existing Ship-to customer that matches the Ship-to customer called for in the file and existing parts with UPC codes that match the UPC codes in the file.
2. Navigate to Tools > Import EDI > click "Run". A message indicating that one file has been processed will appear.
3. Navigate to Sales > Order Reporting > change the Order type to “Internet Sale” > click "View". The order that was created should be the first order in the grid.
4. Navigate to System > History / Performance (versions 15.03.051 and above) or System > View EDI Import History (versions 15.03.032 and below) > the last record in the grid indicates the file that was imported and the order that was created.

EDI import example using the "New Order Same-As Current Order" option

The example below illustrates how the "New Order Same-As Current Order" option may be used.

1. Create 2 Bill-Tos: EDI1 and EDI2.
2. Create 3 Ship-Tos: EDI1, EDI2, and EDI3.
3. Link the three ship-tos to both bill-tos.
4. Create an EDI import with the following setup:
   • New order same-as current order = TRUE
   • New order condition = (field1='BT' OR field1='ST') AND linecount <> 0
   • New order when = Order condition is true
   • New line condition = field1='PN'
   • LINE1:
     • Field = Bill to company ID
     • Conditions = field1='BT'
     • Type = Lookup
     • Lookup Table = Bill to companies
     • Lookup Expression = bi_name=?m.field2
     • Field Expression = bi_id
   • LINE2:
     • Field = Ship to company ID
     • Conditions = field1='ST'
     • Type = Lookup
     • Lookup Table = Ship to companies
     • Lookup Expression = sh_name=?m.field2
     • Field Expression = sh_id
   • LINE3:
     • Field = Part number ID
     • Conditions = field1='PN'
     • Type = Lookup
     • Lookup Table = Item master file
     • Lookup Expression = pr_codenum=?m.field2
     • Field Expression = pr_id
   • LINE4:
     • Field = Quantity Ordered
     • Conditions = field1='PN'
     • Type = Expression
     • Field Expression = field3
   • Explanation - When the first field is BT, lookup the bill-to name from the second field. Same for ST (ship-to) and PN (part number) except PN has a third field to specify the quantity.
5. Run the EDI above with the following sample file below. Notice that the Bill-To is only set twice but this creates 5 orders. This is because the bill-to reference is copied from order-to-order as the new order condition rings true. The new order condition, configured to fire when the bill-to or ship-to changes AND the current order has lines already, is flexible enough to handle this.
   1. BT,EDI1
   2. ST,EDI1
   3. PN,FINI1,1
   4. PN,FINI2,2
   5. PN,FINI3,1
   6. ST,EDI2
   7. PN,FINI1,1
   8. PN,FINI2,2
   9. BT,EDI2
   10. ST,EDI1
   11. PN,FINI1,1
   12. PN,FINI2,2
   13. PN,FINI3,1
   14. ST,EDI2
   15. PN,FINI1,1
   16. PN,FINI2,2
   17. ST,EDI3
   18. PN,FINI1,1
   19. PN,FINI2,2
   20. PN,FINI3,1
6. Validate that 5 orders are created:
   1. BT = EDI1, ST = EDI1, PN = FINI1, FINI2, FINI3
   2. BT = EDI1, ST = EDI2, PN = FINI1, FINI2
   3. BT = EDI2, ST = EDI1, PN = FINI1, FINI2, FINI3
   4. BT = EDI2, ST = EDI2, PN = FINI1, FINI2
   5. BT = EDI2, ST = EDI3, PN = FINI1, FINI2, FINI3

EDI import example that uses the "Delete Line Condition" option

This example will cover an EDI that modifies an existing sales order line and deletes another line on the same order. Note that when using the "Delete Line Condition" option, the EDI Import must contain a parsing rule that identifies the sales order number (to_ordnum) and a parsing rule that identifies the line number (or_linenum) on that order. This process can also be completed with purchase orders. If the Delete Line condition is met for that line, the line will be deleted, otherwise it will be updated based on other rules on the import.

Step 1. Define or identify the sales order to be modified. In this step we will use a text file representing an existing sales order. Below is how the sample file used in this example appears when viewed in Notepad:

Step 2. Setup the EDI Import Form. Navigate to System > Maintenance > EDI Imports > click "Add New" and fill in the EDI Import form as indicated below:

Field	Entry
Name	EDI Delete Line
Source Path	“Select an appropriate path”
Success Path	“Select an appropriate path”
Failure Path	“Select an appropriate path”
Transaction Type	Sales Order
Order type	Sales Order
Sample File	Leave blank for this example
Frequency (mins)	N/A for this example
FTP server	N/A for this example
FTP User Name	N/A for this example
FTP Password	N/A for this example
Field Delimiter	Comma
Other Field Delim.	N/A for this example
Line Delimiter	New line
Other Line Delim.	N/A for this example
New Order Cond.	field1 = "NewOrder"
New Order When	Order condition is true
New Line Condition	field1 = "NewLine"
Skip Line Condition	N/A for this example
Delete Line Condition	field1 = "NewLine" AND field2 = "Delete"
Template	Unchecked
Active	Checked
Accept invalid certificates for FTPS	Checked
Allow Partially Processed Files	Checked

Step 3. Setup the EDI Import Parsing Rules for each line of the import.

Fill out the Edit Parsing Rule form for each of these fields below by first clicking on the "Add" button in the bottom left-hand corner of the EDI Import form and then saving once all information has been entered. This needs to be completed for each line on the EDI Import form.

• 1st rule -

Field	Entry
Field	Order number
Conditions	field1 = "NewOrder"
Type	Lookup
Lookup Table	Sales order header
Lookup Expression	to_descrip = field2 AND to_status = 'c'
Field Expression	to_ordnum

• 2nd rule -

Field	Entry
Field	Line number
Conditions	field1 = "NewLine"
Type	Lookup
Lookup Table	Sales order lines
Lookup Expression	or_toid = ?to_id AND or_prid = (SELECT pr_id FROM dmprod WHERE pr_codenum = field3)
Field Expression	or_linenum

• 3rd rule -

Field	Entry
Field	Quantity ordered
Conditions	field1 = "NewLine" AND field2 = "Update"
Type	Expression
Lookup Table	N/A for this example
Lookup Expression	N/A for this example
Field Expression	field4

Step 4. Run the EDI Import.

1. Ensure that the file is in the source folder as referenced on the EDI import.
2. Navigate to Tools > Import EDI > click "Run". A message indicating that one file has been processed will appear.
3. Navigate to System > History / Performance (versions 15.03.051 and above) or System > View EDI Import History (versions 15.03.032 and below) > the last record in the grid indicates the file that was imported and the order that was created.

Creating customers during EDI Imports

DEACOM offers the ability to import bill-to and ship-to fields for the purpose of creating new customers to support the sales order EDI process. This feature is not meant to be used for the sole purpose of importing master customer records into DEACOM.

The process is detailed in the sections below. Note that the process begins with the selection of the "Lookup Or Create" type and a valid customer record used as a template when creating the new customer in the "Create Same-As" field on the Edit Parsing Rule form.

1. Setup steps for customer import and creation
2. Prior to setting up the necessary steps, users will need to understand the import and creation options as indicate below.
3. New Bill-to records can be created and associated with existing ship-to records.
4. New Ship-to records can be created and associated with existing bill-to records.
5. Both new bill-to and ship-to records can be created.
6. In order to use the import and creation of customers feature, three setup steps must first be performed:
   1. The value in the field "Field" must be set to either bill-to company ID (to_biid) or ship-to company ID (to_shid).
   2. The "Type" field must indicate "Lookup Or Create".
   3. An appropriate ship-to or bill-to customer record must be selected in the "Create Same-As" field. This record will be used as a template when creating a new bill-to or ship to record. Note that the system will only create a new customer record if the parameters set in the "Lookup Expression" field on the edit parsing field fails.

Once this information is set and the EDI import is run, the system will attempt to first lookup the appropriate bill-to/ship-to record and if the lookup fails, the system will use the bill-to/ship-to indicated in the "Create Same-As" field to create a new customer record so that the EDI import may contain and orders may be processed.

Notes:

• An EDI Import may contain a parsing rule for both the bill-to company ID (to_biid) and ship-to company ID (to_shid).
• Deacom Version 16.06.078 further enhanced the Parsing Rule form so that when using the "Create Same-As" button to create a Bill-To or Ship-To via EDI Import the system will prevent the following fields from being imported: Name, Contact, Phone, Ext, DBA, Streets, City, State, Zip, Country, County, Fax, Email, Dear, Salutation, Notes. The code was also changed to add an empty string to the specified fields above so that they are not imported unless explicitly done so by using a parsing rule.

• Customer import and creation processing and example
• An example of a customer import/creation parsing rule is indicated below. As indicated previously, parsing rules are created via the Edit Parsing Rule form when clicking the "Add" button on the EDI Import form.
• Parsing rule example - customer lookup or creation
• The rule below will look up a bill-to company by name and, if the bill-to company name is not found, will use the bill-to company selected in the "Create Same As" field to create a new bill-to company. Background information: The name of the bill-to company selected in the "Create Same As" is "EDITEST" for this example.
• Field: Bill-to company ID
• Conditions: field1='BT'
• Type: Lookup Or Create
• Lookup table: Bill-to companies
• Lookup expression: bi_name=?m.field2
• Create same-as: EDITEST
• Field expression: bi_id

Creating a Ship-To customer using Bill-To customer default values

Beginning in 16.06.46, the Parsing Rule form was enhanced so that when creating a new Ship To Company using the "Lookup or Create" type, and a Bill-To Company is being created as well during the EDI Import, and a Ship-To Company is not specified in the "Create Same As" field, the system will use the values from the new Bill-To Company to set the defaults on the newly created Ship-To Company. See the list below regarding parsing rule exceptions when creating Ship-To Companies via "Lookup or Create" in version 16.06.046 and above.

• If the import profile has a specific parsing rule for any dmship fields, the system will use those instead of copying from the Bill-To Company.
• If a parsing rule exists to override a value on a Bill-To, that overridden value will not propagate to the newly-created Ship-To or to the Order. Similarly, if a parsing rule overrides a value on a Ship-To, that overridden value will not propagate to the Order. For example, if the intent is to override a value on the Bill-To and have it flow to the Ship-To and Order, a parsing rule must exist for each override (one for the Bill-To, one for the Ship-To, and one for the Order).

EDI processing associated with customer creation

In situations where the system creates a new customer record by copying the record indicated in the "Create Same As" field, the system performs the following validations and actions:

• When cloning/copying from the same-as record, the new customer is given a name of IMPORTED EDI CUSTOMER followed by random characters because the user needs to import into bi_name or sh_name to set the customer name.
• The following subtables are cloned when a customer is created same-as: GL Overrides, Authorized Terms, Restricted Selling, Feature Defaults, User Fields, Authorized Buyers, Sales Assignments, Work Flow (clears any completions), Tax Exemptions (ship-to only).
• When order header information is copied to the next order (as indicated by the flag on the order header) any created bill-to and ship-to information copies as well.
• If creating a bill-to or ship-to, EDI will automatically create an association between them. This association is indicated in the dmbillship table.

Note: The Lookup or Create function works best if the Bill-To is resolved before the Ship-To. On flat record import files this is not a common issue as all fields are available on one import record. But with SPS XML data the Address information is presented from multiple import records. Moving the setting to the to_biid before the Ship-To Lookup or Create is invoked, all works well. On XML data files, the Bill-To address loop should be first address loop on all Import files to DEACOM.

Using EDI to apply payments

In DEACOM, EDI imports can be used to apply payments to existing shipped orders. In these cases, the only field that will be edited on a shipped order is the "to_balance" field. Once the "to_balance" is edited, this triggers the system to apply payments to the order and the order is then invoiced.

The details behind this process are listed below.

• Allow EDI to update existing shipped orders but only allow to_balance to be set (to apply a payment). If any other fields besides ordnum and balance are set, throw an EDI exception to write to the log and fail the file.
• When a payment is applied to a shipped order:
  • If it is not invoiced, invoice it.
  • If the order is shipped, post to the same accounts used in Cash Receipts - standard, instead of the pre-payment account that is used in the existing process.
  • If the order is not shipped, use the existing pre-payment logic.
• Update existing EDI sales/purchase orders prior to shipment

Deacom Version 15.04 includes the ability to modify or delete sales order lines during the EDI importing process. This can also be performed for purchase order lines as pert version 16.05.057. The process for deleting a line uses the "Delete Line Condition" field on the EDI Import form. See the field description on the EDI Imports page for additional information on using this field. An example of an EDI Import that modifies a line and deletes a line from a sales order is available via the EDI import example that uses the "Delete Line Condition" option section on this page.

Update EDI sales/purchase orders after shipment and invoicing

Sales and Purchase Order fields that are allowed to be modified using the current Modify Info Option can also be modified using EDI. Note that after the order has been paid, only to_pjid, to_notes, to_user1, to_decrip, to_duedock, to_antcash, and to_confirm can be modified. Note that security exists to control access to the modify information option on sales orders.

Automatically mark purchase orders with non-stocked items as received and invoiced

Purchase orders containing non-stock items may be marked as received and invoiced. To accomplish this, the system invoiced date (tp_invrecv), vendor invoiced date (tp_invvend), or invoice entered date (tp_inventered) must be populated as a result of the EDI Import. If any one of these three fields are populated, the system will receive and invoice the purchase order as if it had been done manually. The purchase order will be received in full and all necessary GL postings will be created. Note that purchase orders containing stocked items cannot be imported, even if the invoice fields mentioned above are populated. Additional information on these three invoice date fields is available via the Invoice page. Note also that Deacom supports the partial receipt of non-stocked items via EDI.

Automatically ship sales orders during EDI Imports

Sales orders can be set to ship automatically via EDI Imports. To accomplish this, the Transaction Type on the EDI Import profile must be set to Sales Order and the following fields, at a minimum, need to be populated as a result of the EDI Import: to_ordnum (order number), to_shipped (shipped date), fi_quant (quantity to be shipped), and fi_prid (part number ID). This configuration works for non lot controlled items. If shipping with lot controlled items, fi_lotnum (System Lot Number) or fi_userlot (User Lot Number) must be populated as well. The sales order will be marked as shipped and all necessary GL and inventory postings will be created.

Automatically receive purchase orders during EDI Imports

Purchase orders can be received automatically via EDI Imports beginning in version 16.07.021, including Purchase Orders with stocked inventory. The ability to automatically receive purchase orders assists with greater efficiency in communicating to trading partners that inventory is available to ship. To accomplish this the Transaction Type on the EDI Import profile must be set to Purchase Order, and the following fields must be set on the EDI Import profile:

• If purchased items are not lot controlled: tp_purnum (match to an existing order), tp_recevd, fi_prid, fi_waid, fi_quant (Should be positive, for example, to receive 5, set fi_quant to 5)
• If purchase items are lot controlled: Same as above plus fi_lotnum (System Lot Number) or fi_userlot (User Lot Number). Optionally, fi_loid (Location ID).

Adjust inventory via EDI Imports

Inventory may be adjusted in or out via EDI Imports. To accomplish this, the Transaction Type on the EDI Import profile must be set to Inventory and the following fields, at a minimum, need to be populated as a result of the EDI Import: fi_prid (part number ID), fi_waid (facility), and fi_quant (quantity being adjusted in or out). If fi_quant is negative, inventory is adjusted out, if fi_quant is positive, inventory is adjusted in. To adjust for lot controlled items, fi_lotnum (System Lot Number) or fi_userlot (User Lot Number) must be populated as well. Optionally, fi_loid (Location ID). Note that inventory transactions will occur as of the date the EDI profile is run, unless a custom date is entered for posting (fi_date).

Ship As Reserved a Sales Order via EDI Import

Inventory already reserved to an order can be used when shipping sales orders via EDI. To accomplish this, a parsing rule for both to_ordnum and to_shipped must be created on the EDI Import routine.

Reserve Inventory to a Sales Order via EDI Import

Inventory can be reserved to a sales order via EDI, even if the order is not being immediately shipped.

In order to reserve via EDI, the EDI Import must contain a parsing rule for to_ordnum, fi_prid, fi_quant, fi_date and at least one of [fi_attrib1, fi_attrib2, fi_attrib3 or fi_masterlot]. Note that fi_date will be used as the reserve date and fi_quant will be used as the reserve quantity, rather than a filter on which lots to reserve. Similar to fi_quant, fi_attrib1-3 and fi_masterlot. These fields indicate values that will be set after the lots are reserved, not values that will be filter on when selecting lots to reserve. All other dtfifo lots (fi_lotnum, fi_serial, fi_catchwgt, etc.) will be used as filters on which lots to select for reserving.

Invoice a shipped sales order via EDI Import

Sales orders that have already been shipped can be invoiced via EDI. To accomplish this, a parsing rule for both to_ordnum and to_invdate must be created on the EDI Import routine.

Ship and invoice a sales order in the same EDI profile/map

Shipped (to_shipped) and Invoiced (to_invdate) rules can exist in the same EDI profile (map). If the shipment action results in a failure for any reason, the system will not attempt to invoice. (Note: The existing functionality where these actions can exist in separate profiles (maps) is still retained.)

Apply NET1 credit card payments to sales orders via EDI

Beginning in version 17.02.013, NET1 payments can now be applied to sales orders via EDI.

When an EDI sales order is created, if the Terms specify a credit card process, and the processor to be used is NET1, the system will use the account vault transaction to execute the credit card transaction at order creation. At this point, the setting in the "Credit Card Process" field on the Term will be evaluated. Note that NET1 credit card payments only apply to shippable orders, an internet order will not trigger the pre-pay process.

Two parsing rules are needed to apply a NET1 payment via EDI Import: one rule to set "to_balance" to zero, and another rule to set the "cc_id" of a credit card that exists in the NET1 account vault for a Bill-To Company. These rules should work whether the import is creating a Sales Order or updating an existing un-shipped Sales Order.

Note: When processing payments in NET1, (beginning in version 17.02.018) the card that is supplied no longer needs to have an address that matches the address on the Bill-To.

Allowing duplicate Bill-to or Ship-to PO numbers on EDI Imports

DEACOM can be set to allow for the same Bill-to and/or Ship-to PO numbers to be imported multiple times in situations where customers reuse these numbers. The process is controlled by fields on the respective Bill-to and Ship-to customer records and is detailed below.

Setting duplicate Bill-to PO numbers

1. Navigate to Sales > Customers.
2. Filter for the appropriate Bill-to customer and hit the "Modify Bill-to" button to open the Edit Bill-To Company form.
3. On the General tab check the "Allow Duplicate Bill-To PO in EDI" field and fill in an appropriate value in the "EDI Bill-To PO Days" field. For example, if duplicate PO numbers are not allowed within 30 days enter 30. The system will check to see if any sales orders within the last 30 days have the same PO number. A 0 indicates the system will not check for duplicates.

Once all changes have been made save and exit the Edit Bill-To Company form to complete the process.

Setting duplicate Ship-to PO numbers

1. Navigate to Sales > Customers.
2. Filter for the appropriate Ship-to customer and hit the "Modify Ship-to" button to open the Edit Ship-To Company form.
3. On the General tab check the "Allow Duplicate Ship-To PO in EDI" field and fill in an appropriate value in the "EDI Ship-To PO Days" field. For example, if duplicate PO numbers are not allowed within 30 days enter 30. The system will check to see if any sales orders within the last 30 days have the same PO number. A 0 indicates the system will not check for duplicates.

Once all changes have been made save and exit the Edit Ship-To Company form to complete the process.

Creating inter-company transfers via EDI imports

Users have the ability to create inter-company transfers when performing EDI imports. The edi import profile will need to contain a line indicating the order type is an inter-company transfer.

Setting Tax Regions on sales orders of new Bill-To/ Ship-To Companies during EDI Import

Beginning in version 16.00.092, EDI imports can now set the Tax Region to a sales order or newly created ship-to or bill-to company. An example of a EDI Parsing Rule setting a Tax Region is listed below. The rules the system will use when setting Tax Regions is as follows:

• If creating an SO, and Lookup or Create is used, and the "Table to Link to" = 'dmship', and a new ST is created, add the Tax Region to the ST.
• If creating an SO, and Lookup or Create is used, and the "Table to Link to" = 'dmship', and an existing ST is found, do not add the Tax Region to the ST.
• If creating an SO, and the "Table to Link to" = 'dttord', add the Tax Region to the order.
• If modifying an SO, and a new Ship-To is selected OR created, replace all existing Tax Regions on the SO with the ones from the newly-selected Ship-To.
• If modifying an SO, and the Ship-To remains the same, retain the existing Tax Regions on the SO.

Note: When performing a Lookup or Create, and an existing BT/ST is found, the system will NOT add a Tax Region Link to the BT/ST. The reason is that there is no safe way to determine if the existing Tax Region Links should remain and, if not, which one(s) should be overwritten.

Business Case: Added for situations where dropship (B2C) orders from a service like Shopify requires creating new Ship-To Customers using the "Lookup or Create" functionality. Due to this, the Ship-To doesn't exist and therefore unable to pull in the Tax Region from the Ship-To.

Tax Region Parsing Rule Example

• Field: Tax Region Id (tl_taid)
• Conditions: !EMPTY(field42)
• This is the field containing the Tax Region (State, for example)
• Type: Lookup
• Lookup Expression: LEFT(ta_name, 2) = field42 AND ta_active = 1 UNION ALL SELECT * FROM dmtax WHERE LEFT(ta_name, 2) = 'ZZ' ORDER BY ta_name ASC
• Field Expression: IIF(LEFT(ta_name, 2) = 'ZZ', 0, ta_id)

Note: The Tax Region fields (dmtaxlinkn table) are only available to the "Field" option on the Edit Parsing Rule form when the Transaction Type on the ediadd form = 'Sales Order'. These fields are only able to be imported into when using the Lookup Or Create option on Bill-To's or Ship-To's. The tax regions on the sales orders that are imported will follow the standard tax region logic, which typically pulls the tax regions from the ship-to company.

Setting General Ledger Overrides for new Bill-To/Ship-To Companies during EDI Import

Deacom Version 16.06.047 and higher allows users to set GL Overrides, during EDI Imports, on newly created Bill-Tos and Ship-Tos by adding 3 GL Override fields (ov_table, ov_pos, ov_override) to the dropdown in the "Field" field on the Edit Parsing Rule form. These fields are only available when the "Transaction Type" field on the EDI Import form is set to "Sales Order" and only when using the Lookup or Create option on Bill-To/Ship-To records.

Only two expressions are available to the "Table" (ov_table) rule: 'dmbill' and 'dmship'. The table names must be surrounded by quotes in the "Expression" field.

Running EDI Imports

Once the EDI Import form has been completed and saved, the EDI import can be run via Tools > Import EDI. A couple tips regarding the setup and processing of EDI Imports:

It is generally a good idea to import Sales Orders with an "Order Type" of "Internet Order". That way, users may review, update, and finalize the details before saving it as an official Sales Order, which impacts MRP.

While multiple Sales Orders can be included in an single import, it is a best practice to use separate imports for each order. That way, if an error occurs, it is easy to identify where, why, and what orders were affected.

Handling EDI Import Errors

DEACOM performs a number of different data validations during an EDI import. If a file is rejected, the file name and error message will be recorded in the EDI History log.

See Common EDI Errors for a comprehensive list of EDI Import errors.

Notifying Users of EDI Failures via System Triggers

In addition to the EDI Import History log, a trigger may be created to automatically send an email when an EDI file has failed to import correctly. The specific parameters and field details for this trigger are indicated below. Additional information on the general setup and use of triggers is available via System > Maintenance > Triggers.

To setup a trigger that will notify users when an EDI file has failed to import, navigate to System > Maintenance > Triggers, click the "Add New" button and fill in the "Edit Trigger" form as indicated in the table below.

Field/Flag/Button	Description
Name	Email EDI Import Failures
Trigger Event	Insert
Table	EDI History
Output	Plain Text Email
Subject	Failed EDI Import - <<go.compname>>
Destination	Enter appropriate email address. Use semicolons between multiple email addresses.
Conditions	eh_outcome = 'Failure'
Message	EDI Import "<<ALLT(ed_name)>>" failed to process file "<<RIGHT(eh_file, LEN(eh_file) - RAT('\', eh_file))>>" at <<eh_date>> <<eh_time>>.  <<eh_msg>>  Please visit https://help.deacom.com/index.php/Common_EDI_Errors for help with this error.
Custom Query	SELECT ed_name FROM dmedi WHERE ed_id = <<eh_edid>>
Active	Checked
Template	Unchecked

When all the appropriate information has been entered, click the "Save" and "Exit" buttons to commit the changes and complete the process.

FAQ & Diagnostic Tips

• Tip: The EDI Import History report will insert a record in the report anytime an order splitter is utilized/run, usually via an external program.
• My EDI Import History Report is showing my EDI import is failing. The message is "Access to the path is denied." What is the problem?
• Confirm the deacom.automation.service is running on the server. If the password for the login account has been changed, it needs to be changed on the Logon Tab of the service or the service will not start. Confirm you can stop and restart the service. Make sure scanner users are off the system before restarting. If unable to restart, reboot the server.
• On failing Sales Order EDI Imports, I receive the following message text in EDI Import History: "The data type for the User Field Name field is incompatible with the value assigned to it."
• Check your user field names in Tools > Edit User Fields to verify that the name of the field listed in the message text has not changed. If a field is set up on the parsing conditions in System > Maintenance > EDI Imports, but the field name has changed, the import will fail. Also, if you have recently updated to a new version (14.3 to 14.4), field names may have changed. Use System > Data Dictionary to search failing fields and verify that they are still active.

Does DEACOM partially process EDI files?

Yes. Instead of throwing exceptions, DEACOM now logs them in EDI History. There is a variable that tracks whether or not the order failed. If it failed, the file will be added to the failed orders list. In DEACOM, when all orders are finished processing, there is a list of successes and failures. The system loops through these to create the success file and the fail file. When a file fails, the system appends a time stamp to it. This way a user can edit the failed file, move it to the source folder, and reprocess. If it fails again the system replaces the time stamp with a new one. Otherwise, it will be moved it to the success folder. If for some reason there is the same name in the success folder, it will append a "_2".

How does the system report EDI failures if a Bill-to or Ship-to lookup/creation fails?

The system will only write the Bill-to/Ship-to failure to the EDI Import history and will not write each order line as a failure.

Does DEACOM require the use of a VAN to facilitate EDI transactions with our trading partners?

DEACOM does not require a VAN to facilitate EDI transactions however many trading partners do require a VAN to be used.

Does DEACOM facilitate AS2 transactions?

DEACOM does not have an AS2 communication built into the software. If this is required by a trading partner a VAN can be used to complete the transaction out of DEACOM.

Can I look-up a customer part cross reference when I only have the part number?

Yes, you would first set the Part number ID and Bill-to company ID. Then you can look up and set the customer part cross reference. The lookup expression would be cu_prid = ?m.or_prid AND cu_biid = ?m.to_biid and the field expression would be cu_id.

How do I use today's date to populate a field?

The date() function will return the current date. Select field, Conditions should be blank, Type is Expression, and Field expression is date().

Does DEACOM maintain remit to information that can be used in EDI Transactions?

Yes, the System > Options > Company tab contains a remit to identifier field which is used for EDI purposes, and in particular when using SPS Commerce as the customer's VAN for EDI transactions. Specifically, this field is necessary when an Invoice is triggered on an sales order identifying an ID associated with the Remit To on the invoice. This value would be preset on the sales order and returned on an invoice indicating where the customer should remit payments to. This field is a text box.

How does EDI handle sales order promotions?

During EDI processing, the system will reorder the "Promos Based On" if they are not for all items. If "Promo On" is not All Items, this will reorder them.

How can I import and EDI file containing multiple order types?

In EDI Imports, order type can now be overridden via an EDI parsing rule. The system is designed to set the order type early on when creating the order which allows it be overridden with a parsing rule if one exists.

Can I indicate taxes owed for order lines when running 810 imports?

The taxamt 1/2 variables can be used in triggers to reference the taxes owed for each line item. The fields are calculated as or_exten * ta_rate.

Do import maps need to have unique names?

Yes, if a user enters a name that has already been used and clicks the "Import" button, the system will prompt the user that the name must be unique.